/*
 * Created by SharpDevelop.
 * User: florian
 * Date: 17.06.2006
 * Time: 09:36
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.ComponentModel;
using System.Text;
using System.Data;

namespace SqlScriptCreator.Generators
{
	/// <summary>
	/// Description of CursorGenerator.
	/// </summary>
	[DefaultPropertyAttribute("CursorName")]
	public class CursorGenerator : IScriptGenerator
	{
		
		private String _cursorName = "curQuery";

		[CategoryAttribute("Cursor settings"),
		 DescriptionAttribute("The name of the generated cursor.")]
		public string CursorName {
			get {
				return _cursorName;
			}
			set {
				_cursorName = value;
			}
		}
		private String _variablePrefix;
		
		[CategoryAttribute("Cursor settings"),
		 DescriptionAttribute("Characters that will be prepended to every generated variable.")]
		public string VariablePrefix {
			get {
				return _variablePrefix;
			}
			set {
				_variablePrefix = value;
			}
		}
		private String _variableSuffix;
		
		[CategoryAttribute("Cursor settings"),
		 DescriptionAttribute("Characters that will be appended to every generated variable.")]
		public string VariableSuffix {
			get {
				return _variableSuffix;
			}
			set {
				_variableSuffix = value;
			}
		}
	
		public CursorGenerator()
		{
		}
		
		override public string ToString() {
			return "Cursor iteration with print";
		}

        public string generateScript(String query, DataTable dtQueryResult, DataTable dtParameters)
        {
            StringBuilder sb = new StringBuilder();
            String[] parameterNames = new String[dtParameters.Rows.Count];
            String[] parameterNamesWithConvert = new String[dtParameters.Rows.Count];
            int counter = 0;

            foreach (DataRow row in dtParameters.Rows)
            {
                int columnSize = Convert.ToInt32(row["ColumnSize"]);
                String dataType = row["DataTypeName"].ToString();
                String columnName = row["BaseColumnName"].ToString();

                if (SqlTools.isValidForLocalVariable(dataType))
                {
                    parameterNames[counter] = "@" + VariablePrefix + columnName + VariableSuffix;
                    parameterNamesWithConvert[counter] = "ISNULL(CONVERT(VARCHAR(30), " + parameterNames[counter] + "), '')";
                    sb.AppendLine("DECLARE " + parameterNames[counter] + " " + SqlTools.getDatatypeExpression(dataType, columnSize));
                    counter++;
                }
            }

            sb.AppendLine("");
            sb.AppendLine("DECLARE " + CursorName + " CURSOR FOR");
            sb.AppendLine(query);
            sb.AppendLine("");
            sb.AppendLine("OPEN "+CursorName);
            sb.AppendLine("");
            sb.Append("FETCH NEXT FROM "+CursorName+" INTO ");
            sb.AppendLine(String.Join(", ", parameterNames));
            sb.AppendLine("");
            sb.AppendLine("WHILE @@FETCH_STATUS = 0");
            sb.AppendLine("BEGIN");
            sb.Append("\tPRINT ");
            sb.AppendLine(String.Join(" + '  ' +\r\n\t\t", parameterNamesWithConvert));
            sb.Append("\tFETCH NEXT FROM " + CursorName + " INTO ");
            sb.AppendLine(String.Join(", ", parameterNames));
            sb.AppendLine("END");
            sb.AppendLine("");
            sb.AppendLine("CLOSE " + CursorName);
            sb.AppendLine("DEALLOCATE " + CursorName);
            return sb.ToString();
        }

	}
}
